﻿//2009-12-16    告警設定相關
//2010-05-17    更新監控點資料API       By 應翰
//2010-09-02    新增歷史通訊告警API     By Victor

//2010-12-08   udpate by May
//1.更新strQueryCommAlarmSQL，不抓sitename 
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

//Using Namespace
using System.Data;
using System.Data.SqlClient;

namespace DataAccess
{
    public class AccessAlarm
    {
        private string strSCADADSN = System.Configuration.ConfigurationManager.AppSettings["strDSN"];
        string strSQL;        

        //查詢即時告警
        #region public DataTable QueryCurAlarm()        //查詢即時告警資料
        /// <summary>
        /// 查詢即時告警資料
        /// </summary>
        /// <returns>回傳即時告警資料</returns>
        public DataTable QueryCurAlarm()
        {
            try
            {
                strSQL = "Select *, Case bytAlarmType When '1' Then '重要告警' When '2' Then '次要告警' Else '無' End As AlarmType From CurAlarm_data Join GroupConfig_cfg On CurAlarm_data.SiteID = GroupConfig_cfg.SiteID and CurAlarm_data.GrpNo = GroupConfig_cfg.GrpNo Join Web_Site On CurAlarm_data.SiteID = Web_Site.SiteID";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryCurAlarm", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        //查詢歷史告警資料
        #region public DataTable QueryHisAlarm()        //查詢歷史告警資料
        /// <summary>
        /// 查詢歷史告警資料
        /// </summary>
        /// <returns>回傳歷史告警資料</returns>
        public DataTable QueryHisAlarm()
        {
            try
            {
                strSQL = "Select *, Case bytAlarmType When '1' Then '重要告警' When '2' Then '次要告警' Else '無' End As AlarmType From HisAlarm_data Join GroupConfig_cfg On HisAlarm_data.SiteID = GroupConfig_cfg.SiteID and HisAlarm_data.GrpNo = GroupConfig_cfg.GrpNo Join Web_Site On HisAlarm_data.SiteID = Web_Site.SiteID";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryAlarmLog", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion

        //查詢即時通訊告警資料
        #region public DataTable QueryCurCommData()        //查詢即時通訊狀態資料
        /// <summary>
        /// 查詢即時告警查詢資料
        /// </summary>
        /// <returns>回傳即時告警查詢警資料</returns>
        public DataTable QueryCurCommData()
        {
            try
            {
                strSQL = "Select * from CurComm_data where DevNo = 0  ";
                return new CommonFun().GenDataTable(strSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryAlarmLog", ex.ToString(), strSQL);
                return null;
            }
        }
        #endregion


        //查詢歷史通訊告警      相關Table：HisCommAlarm_data       相關網頁：QueryHisCommAlarm.aspx
        string strQueryCommAlarmSQL = "Select TOP(100) * From  HisCommAlarm_data";
        #region public DataTable QueryHisCommAlarm()        //查詢前100筆歷史通訊告警資料
        /// <summary>
        /// 查詢前100筆歷史通訊告警資料（避免回傳全部會造成效能下降）
        /// </summary>
        /// <returns>回傳前100筆歷史告警資料</returns>
        public DataTable QueryHisCommAlarm()
        {
            try
            {
                //strSQL = "Select * From  HisCommAlarm_data Join GroupConfig_cfg On HisCommAlarm_data.SiteID = GroupConfig_cfg.SiteID Join Web_Site On  HisCommAlarm_data.SiteID = Web_Site.SiteID";
                return new CommonFun().GenDataTable(strQueryCommAlarmSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryHisCommAlarm", ex.ToString(), strQueryCommAlarmSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QueryHisCommAlarm(string AlarmStartDate, string AlarmEndDate)-1        //查詢歷史通訊告警資料
        /// <summary>
        /// 查詢通訊歷史告警
        /// </summary>
        /// <param name="AlarmStartDate">告警查詢起始時間</param>
        /// <param name="AlarmEndDate">告警查詢結束時間</param>
        /// <returns>回傳通訊告警相關資料</returns>
        public DataTable QueryHisCommAlarm(string AlarmStartDate, string AlarmEndDate)
        {
            try
            {
                strQueryCommAlarmSQL += " Where datAlarm1 Between '" + AlarmStartDate + "' and '" + AlarmEndDate + "'";
                return new CommonFun().GenDataTable(strQueryCommAlarmSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryHisCommAlarm-1", ex.ToString(), strQueryCommAlarmSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QueryHisCommAlarm(string AlarmStartDate, string AlarmEndDate, string SiteID)-2        //查詢歷史通訊告警資料
        /// <summary>
        /// 查詢通訊歷史告警
        /// </summary>
        /// <param name="AlarmStartDate">告警查詢起始時間</param>
        /// <param name="AlarmEndDate">告警查詢結束時間</param>
        /// <param name="SiteID">案場ID</param>
        /// <returns>回傳通訊告警相關資料</returns>
        public DataTable QueryHisCommAlarm(string AlarmStartDate, string AlarmEndDate, string SiteID)
        {
            try
            {
                strQueryCommAlarmSQL += " Where datAlarm1 Between '" + AlarmStartDate + "' and '" + AlarmEndDate + "' and HisCommAlarm_data.SiteID='" + SiteID + "'";
                return new CommonFun().GenDataTable(strQueryCommAlarmSQL);
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("QueryHisCommAlarm-2", ex.ToString(), strQueryCommAlarmSQL);
                return null;
            }
        }
        #endregion


        //查詢告警設定資料
        #region public DataTable QueryTagConfig()        //查詢TagConfig資料
        public DataTable QueryTagConfig()
        {
            try
            {
                this.strSQL = "Select * From TagConfig_cfg";
                return new CommonFun().GenDataTable(this.strSQL);
            }
            catch (Exception exception)
            {
                new CommonFun().AddErrorMsg("QueryTagConfig-1", exception.ToString(), this.strSQL);
                return null;
            }
        }
        #endregion

        #region public bool UpdateTagAlarm        //更新監控點告警資料
        public bool UpdateTagAlarm(string SiteID, string GrpNo, string ObjNo, string AlarmType, string Normal, string AlarmHLimit, string AlarmLLimit
            , string AlarmTime1, string AlarmTime2, int TimeNotify, int TimeDelay, string SMSEmpNo, string MailEmpNo, string OSSFlag)        //更新監控點告警資料
        {
            try
            {
                DateTime dtNow = DateTime.Now;
                strSQL = " Update TagConfig_cfg";
                strSQL = strSQL + " set bytAlarmType='" + AlarmType + "', bytNormal='" + Normal + "', sngAlarmHLimit='" + AlarmHLimit + "', sngAlarmLLimit='" + AlarmLLimit + "'";
                strSQL = strSQL + " , bytAlarmTime1='" + AlarmTime1 + "', bytAlarmTime2='" + AlarmTime2 + "', intTimeNotify=" + TimeNotify + ", intTimeDelay=" + TimeDelay;
                strSQL = strSQL + " , bytSMSEmpNo='" + SMSEmpNo + "', byteMailEmpNo='" + MailEmpNo + "', OSSFlag='" + OSSFlag + "'";
                strSQL = strSQL + " Where SiteID ='" + SiteID + "' and GrpNo ='" + GrpNo + "' and ObjNo ='" + ObjNo + "'";
                bool flag = true;
                if (new CommonFun().ExeSQL(this.strSQL))
                {
                    this.strSQL = "Update MsgPipe_data Set bytIsRenew ='1', UpdateTime = '" + dtNow.ToString("s") + "' Where strTableName = 'TagConfig_cfg'";
                    if (!new CommonFun().ExeSQL(this.strSQL))
                    {
                        flag = false;
                    }
                    this.strSQL = "Update MsgPipe_data Set bytIsRenew ='1', UpdateTime = '" + dtNow.ToString("s") + "'  Where strTableName = 'WebAlarmConfig'";
                    if (!new CommonFun().ExeSQL(this.strSQL))
                    {
                        flag = false;
                    }
                    this.strSQL = " Update MsgPipe_data Set bytIsRenew ='1', UpdateTime = '" + dtNow.ToString("s") + "' Where strTableName = 'WebUser'";
                    if (!new CommonFun().ExeSQL(this.strSQL))
                    {
                        flag = false;
                    }
                }
                else
                {
                    flag = false;
                }
                return flag;
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("UpdateTagAlarm", ex.ToString(), strSQL);
                return false;
            }
        }
        #endregion

        #region public DataTable QueryPLCConfig        //查詢PLC資料
        public DataTable QueryPLCConfig()
        {
            try
            {
                this.strSQL = "Select * From MPLCConfig_cfg";
                return new CommonFun().GenDataTable(this.strSQL);
            }
            catch (Exception exception)
            {
                new CommonFun().AddErrorMsg("QueryPLCConfig", exception.ToString(), this.strSQL);
                return null;
            }
        }
        #endregion

        #region public DataTable QueryDeviceConfig()        //查詢設備資料
        public DataTable QueryDeviceConfig()
        {
            try
            {
                this.strSQL = "Select * From DevConfig_cfg";
                return new CommonFun().GenDataTable(this.strSQL);
            }
            catch (Exception exception)
            {
                new CommonFun().AddErrorMsg("QueryDeviceConfig", exception.ToString(), this.strSQL);
                return null;
            }
        }
        #endregion

        #region public bool UpdatePLCAlarm        //更新PLC告警資料
        public bool UpdatePLCAlarm(string SiteID, int intTimeNotify, int intTimeDelay, string bytSMSEmpNo, string byteMailEmpNo, string MPLCNo)        //更新PLC告警資料
        {
            try
            {
                DateTime dtNow = DateTime.Now;
                strSQL = " Update MPLCConfig_cfg";
                strSQL = strSQL + " set intTimeNotify=" + intTimeNotify + ", intTimeDelay=" + intTimeDelay;
                strSQL = strSQL + " , bytSMSEmpNo='" + bytSMSEmpNo + "', byteMailEmpNo='" + byteMailEmpNo + "'";
                strSQL = strSQL + " Where SiteID ='" + SiteID + "' and MPLCNo ='" + MPLCNo + "'";
                bool flag = true;
                if (new CommonFun().ExeSQL(this.strSQL))
                {
                    this.strSQL = "Update MsgPipe_data Set bytIsRenew ='1', UpdateTime = '" + dtNow.ToString("s") + "' Where strTableName = 'MPLCConfig_cfg'";
                    if (!new CommonFun().ExeSQL(this.strSQL))
                    {
                        flag = false;
                    }
                    this.strSQL = "Update MsgPipe_data Set bytIsRenew ='1', UpdateTime = '" + dtNow.ToString("s") + "'  Where strTableName = 'WebAlarmConfig'";
                    if (!new CommonFun().ExeSQL(this.strSQL))
                    {
                        flag = false;
                    }
                    this.strSQL = " Update MsgPipe_data Set bytIsRenew ='1', UpdateTime = '" + dtNow.ToString("s") + "' Where strTableName = 'WebUser'";
                    if (!new CommonFun().ExeSQL(this.strSQL))
                    {
                        flag = false;
                    }
                }
                else
                {
                    flag = false;
                }
                return flag;
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("UpdatePLCAlarm", ex.ToString(), strSQL);
                return false;
            }
        }
        #endregion

        #region public bool UpdateDevAlarm        //更新設備告警資料
        public bool UpdateDevAlarm(string SiteID, int intTimeNotify, int intTimeDelay, string bytSMSEmpNo, string byteMailEmpNo, string DevNo, string MPLCNo)        //更新Dev告警資料
        {
            try
            {
                DateTime dtNow = DateTime.Now;
                strSQL = " Update DevConfig_cfg";
                strSQL = strSQL + " set intTimeNotify=" + intTimeNotify + ", intTimeDelay=" + intTimeDelay;
                strSQL = strSQL + " , bytSMSEmpNo='" + bytSMSEmpNo + "', byteMailEmpNo='" + byteMailEmpNo + "'";
                strSQL = strSQL + " Where SiteID ='" + SiteID + "' and MPLCNo ='" + MPLCNo + "' and DevNo='" + DevNo + "'";
                bool flag = true;
                if (new CommonFun().ExeSQL(this.strSQL))
                {
                    this.strSQL = "Update MsgPipe_data Set bytIsRenew ='1', UpdateTime = '" + dtNow.ToString("s") + "' Where strTableName = 'DevConfig_cfg'";
                    if (!new CommonFun().ExeSQL(this.strSQL))
                    {
                        flag = false;
                    }
                    this.strSQL = "Update MsgPipe_data Set bytIsRenew ='1', UpdateTime = '" + dtNow.ToString("s") + "'  Where strTableName = 'WebAlarmConfig'";
                    if (!new CommonFun().ExeSQL(this.strSQL))
                    {
                        flag = false;
                    }
                    this.strSQL = " Update MsgPipe_data Set bytIsRenew ='1', UpdateTime = '" + dtNow.ToString("s") + "' Where strTableName = 'WebUser'";
                    if (!new CommonFun().ExeSQL(this.strSQL))
                    {
                        flag = false;
                    }
                }
                else
                {
                    flag = false;
                }
                return flag;
            }
            catch (Exception ex)
            {
                new CommonFun().AddErrorMsg("UpdateDevAlarm", ex.ToString(), strSQL);
                return false;
            }
        }
        #endregion
    }
}
